package com.autonavi.yunda.yunji.service;

import com.autonavi.yunda.yunji.common.utils.JsonUtils;
import com.autonavi.yunda.yunji.core.enums.DatasourceType;
import com.autonavi.yunda.yunji.core.model.dto.DatasourceConfigDTO;
import com.autonavi.yunda.yunji.core.service.DBDataSourceConfigService;
import com.autonavi.yunda.yunji.core.service.dbfactory.DbFactoryService;
import com.autonavi.yunda.yunji.core.vo.datasource.DbTableInfo;
import com.fasterxml.jackson.core.type.TypeReference;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author cizhong.wcs
 * @date 2023/8/28 19:19
 */
@Component
public class DataSourceInfoService {
    private static final String TABLE_NAME = "tableName";
    private static final String INDEX_INFO = "indexInfo";

    private static final String MYSQL_SINGLE_TABLE_NAME_QUERY =
            "select table_comment as tableDesc " +
                    "from information_schema.tables " +
                    "where (table_type='base table' or table_type='BASE TABLE') and table_schema= :instanceName and table_name = :tableName;";

    private static final String MYSQL_SINGLE_TABLE_SCHEMA_QUERY =
            String.format("select TABLE_NAME as %s,COLUMN_NAME as columnName,DATA_TYPE as type,COLUMN_COMMENT as columnDesc " +
                    "from information_schema.columns " +
                    "where UPPER(table_schema) = UPPER(:instanceName) and table_name = :tableName;", TABLE_NAME);

    //    (UPPER(table_schema) = UPPER(:instanceName) or UPPER(table_schema) = UPPER(:instanceName)
    private static final String MYSQL_SINGLE_TABLE_NAME_MAP_QUERY =
            String.format("select TABLE_NAME as %s,table_comment as tableDesc " +
                    "from information_schema.tables " +
                    "where (table_type='base table' or table_type='BASE TABLE') and UPPER(table_schema) = UPPER(:instanceName) and table_name = :tableName;", TABLE_NAME);
    private static final String MYSQL_TABLE_NAME_QUERY =
            String.format("select table_name as %s,table_comment as tableDesc " +
                    "from information_schema.tables " +
                    "where (table_type='base table' or table_type='BASE TABLE') and UPPER(table_schema) = UPPER(:instanceName) ;", TABLE_NAME);

    private static final String MYSQL_ALL_TABLE_SCHEMA_QUERY =
            String.format("select TABLE_NAME as %s,COLUMN_NAME as columnName,DATA_TYPE as type,COLUMN_COMMENT as columnDesc " +
                    "from information_schema.columns " +
                    "where UPPER(table_schema) = UPPER(:instanceName) ;", TABLE_NAME);

    //最多只取500个表，防止前端页面崩溃
    private static final String PGSQL_TABLE_NAME_QUERY =
            "SELECT\n" +
                    "\trelname AS \"tableName\",\n" +
                    "\tCAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS \"tableDesc\" \n" +
                    "FROM\n" +
                    "\tpg_class c\n" +
                    "WHERE\n" +
                    "\trelname IN ( SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER  BY  tablename limit 500)\n" +
                    "order by relname;";
    private static final String PGSQL_ALL_TABLE_SCHEMA_QUERY =
            "SELECT tmp.\"tableName\", tmp.\"columnName\", tmp.\"type\", d.description AS \"columnDesc\" from(\n" +
                    "SELECT c.relname AS \"tableName\", a.attname AS \"columnName\", t.typname AS \"type\",a.attrelid,a.attnum\n" +
                    "FROM pg_class c,pg_attribute a,pg_type t\n" +
                    "WHERE a.attnum > 0 \n" +
                    "AND a.attrelid = c.oid \n" +
                    "AND a.atttypid = t.oid \n" +
                    "AND c.relname IN ( SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER  BY  tablename limit 500)\n" +
                    "ORDER BY \n" +
                    "c.relname,\n" +
                    "a.attnum) tmp LEFT JOIN pg_description d on \n" +
                    "d.objoid = tmp.attrelid \n" +
                    "AND d.objsubid = tmp.attnum;";

    @Resource
    private DbFactoryService dbFactoryService;
    @Resource
    private DBDataSourceConfigService dbDataSourceConfigService;

    public List<DbTableInfo> getAllTableInfo(String dbName) {
        NamedParameterJdbcTemplate dbTemplate = new NamedParameterJdbcTemplate(dbFactoryService.getInstance(dbName));
        DatasourceConfigDTO dto = dbDataSourceConfigService.selectOneByDbNameOrThrow(dbName);
        DatasourceType dataSourceType = dto.getType();
        String dataSourceConfig = dto.getConfig();
        List<DbTableInfo> allTableInfos;
        List<Map<String, Object>> allTableNameList;
        List<Map<String, Object>> allColumnsList;
        if (DatasourceType.PGSQL.equals(dataSourceType)) {
            //拿到全部表名
            allTableNameList = dbTemplate.queryForList(PGSQL_TABLE_NAME_QUERY, new HashMap<>(8));
            allTableInfos = JsonUtils.from(JsonUtils.toString(allTableNameList), new TypeReference<>() {
            });
            allColumnsList = dbTemplate.queryForList(PGSQL_ALL_TABLE_SCHEMA_QUERY, new HashMap<>(8));

        } else {
            Map<String, String> params = new HashMap<>(8);
            params.put("instanceName", dbFactoryService.getInstanceName(dbName, dataSourceType, dataSourceConfig));
            //拿到全部表名
            allTableNameList = dbTemplate.queryForList(MYSQL_TABLE_NAME_QUERY, params);
            allTableInfos = JsonUtils.from(JsonUtils.toString(allTableNameList), new TypeReference<>() {
            });
            allColumnsList = dbTemplate.queryForList(MYSQL_ALL_TABLE_SCHEMA_QUERY, params);
        }
        Map<Object, List<Map<String, Object>>> tableNameColumnInfosMap = allColumnsList.stream()
                .collect(Collectors.groupingBy(columnInfo -> columnInfo.get(TABLE_NAME)));
        //TODO 索引数据待获取
        allTableInfos.forEach(dbTableInfo -> {
            if (tableNameColumnInfosMap.containsKey(dbTableInfo.tableName)) {
                dbTableInfo.columnInfo
                        = JsonUtils.from(JsonUtils.toString(tableNameColumnInfosMap.get(dbTableInfo.tableName)),
                        new TypeReference<>() {
                        });
            }
        });

        return allTableInfos;
    }

    public List<DbTableInfo> getSingleTableInfoList(String dbName, String tableName) {
        NamedParameterJdbcTemplate dbTemplate = new NamedParameterJdbcTemplate(dbFactoryService.getInstance(dbName));
        DatasourceConfigDTO dto = dbDataSourceConfigService.selectOneByDbNameOrThrow(dbName);
        DatasourceType dataSourceType = dto.getType();
        String dataSourceConfig = dto.getConfig();
        List<DbTableInfo> tableInfos;
        List<Map<String, Object>> tableNameList;
        List<Map<String, Object>> columnsList;
        if (DatasourceType.PGSQL.equals(dataSourceType)) {
            //拿到全部表名 TODO
            tableNameList = dbTemplate.queryForList(PGSQL_TABLE_NAME_QUERY, new HashMap<>(8));
            tableInfos = JsonUtils.from(JsonUtils.toString(tableNameList), new TypeReference<>() {
            });
            columnsList = dbTemplate.queryForList(PGSQL_ALL_TABLE_SCHEMA_QUERY, new HashMap<>(8));

        } else {
            Map<String, String> params = new HashMap<>(8);
            params.put("instanceName", dbFactoryService.getInstanceName(dbName, dataSourceType, dataSourceConfig));
            params.put("tableName", tableName);
            //拿到表名与表描述的map
            tableNameList = dbTemplate.queryForList(MYSQL_SINGLE_TABLE_NAME_MAP_QUERY, params);
            tableInfos = JsonUtils.from(JsonUtils.toString(tableNameList), new TypeReference<>() {
            });
            columnsList = dbTemplate.queryForList(MYSQL_SINGLE_TABLE_SCHEMA_QUERY, params);
        }
        Map<Object, List<Map<String, Object>>> tableNameColumnInfosMap = columnsList.stream()
                .collect(Collectors.groupingBy(columnInfo -> columnInfo.get(TABLE_NAME)));
        //TODO 索引数据待获取
        tableInfos.forEach(dbTableInfo -> {
            if (tableNameColumnInfosMap.containsKey(dbTableInfo.tableName)) {
                dbTableInfo.columnInfo
                        = JsonUtils.from(JsonUtils.toString(tableNameColumnInfosMap.get(dbTableInfo.tableName)),
                        new TypeReference<>() {
                        });
            }
        });
        return tableInfos;
    }

    public DbTableInfo getTableInfo(String dbName, String tableName) {
        NamedParameterJdbcTemplate dbTemplate = new NamedParameterJdbcTemplate(dbFactoryService.getInstance(dbName));
        DatasourceConfigDTO dto = dbDataSourceConfigService.selectOneByDbNameOrThrow(dbName);
        DatasourceType dataSourceType = dto.getType();
        String dataSourceConfig = dto.getConfig();
        DbTableInfo dbTableInfo = new DbTableInfo();
        String tableDesc = null;
        List<Map<String, Object>> columnList = null;
        if (DatasourceType.PGSQL.equals(dataSourceType)) {
            //TODO
        } else {
            Map<String, String> params = new HashMap<>(2);
            params.put("instanceName", dbFactoryService.getInstanceName(dbName, dataSourceType, dataSourceConfig));
            params.put("tableName", tableName);
            tableDesc = dbTemplate.queryForObject(MYSQL_SINGLE_TABLE_NAME_QUERY, params, String.class);
            columnList = dbTemplate.queryForList(MYSQL_SINGLE_TABLE_SCHEMA_QUERY, params);
        }

        //TODO 索引数据待获取
        dbTableInfo.tableName = tableName;
        dbTableInfo.tableDesc = tableDesc;
        dbTableInfo.columnInfo = JsonUtils.from(JsonUtils.toString(columnList), new TypeReference<>() {
        });
        return dbTableInfo;
    }

}
